import pymysql
import pickle
from  datetime import *
import time


def login(id, password):  # 登陆
    sql = "SELECT * FROM 用户 WHERE " \
          "用户名 = %s AND 密码 = '%s'" % (id, password)
    try:
        cursor.execute(sql)
        user = cursor.fetchone()
        return user[2]
    except:
        return -1



def s_insert(student, password):  # 插入学生信息
    sql1 = "insert into 学生(学号,姓名,性别,出生日期,入学时间,专业,班级,院系) " \
           "values(%d,'%s','%s','%s','%s','%s','%s','%s')" % (student[0], student[1], student[2],
                                                              student[3], student[4], student[5],
                                                              student[6], student[7])
    sql2 = "insert into 用户(用户名,密码,权限)" \
           "values(%d,'%s','学生')" % (student[0], password)
    try:
        cursor.execute(sql1)
        cursor.execute(sql2)
        conn.commit()
        return 1
    except:
        return -1


def t_insert(teacher, password):  # 插入教师信息
    sql1 = 'insert into 教师(工号,姓名,性别,职称,专业)' \
           'values(%d,\'%s\',\'%s\',\'%s\',\'%s\')' % (teacher[0], teacher[1], teacher[2],
                                                       teacher[3], teacher[4])
    sql2 = "insert into 用户(用户名,密码,权限)values(%d,'%s','教师')" % (teacher[0], password)
    try:
        cursor.execute(sql1)
        cursor.execute(sql2)
        conn.commit()
        return 1
    except:
        return -1


def c_insert(course):
    sql = "insert into 实验课程(名称,工号,课程类型,上课时间,上课地点,学时,学分)" \
          "values('%s',%s,'%s','%s','%s',%s,%s)" % (course[0], course[1], course[2],
                                                    course[3], course[4], course[5],
                                                    course[6])
    try:
        cursor.execute(sql)
        conn.commit()
        return 1
    except:
        return -1


def s_get_course(student_id):  # 获取学生课程信息
    sql = 'SELECT 编号,名称,上课地点,课程类型,上课时间,上课地点,学时,学分 ' \
          'FROM 实验课程 NATURAL JOIN 选课信息 ' \
          'WHERE 学号 = %s' % student_id
    try:
        cursor.execute(sql)
        course = cursor.fetchall()
        return course
    except:
        return -1


def t_get_course(teacher_id):
    sql = 'SELECT 编号,名称,上课地点,课程类型,上课时间,上课地点,学时,学分 ' \
          'FROM 实验课程 NATURAL JOIN 教师 ' \
          'WHERE 工号 = %s' % teacher_id
    try:
        cursor.execute(sql)
        course = cursor.fetchall()
        return course
    except:
        return -1


def get_snum_in_course(course_id):
    sql = 'SELECT count(*) FROM 选课信息 WHERE 编号 = %s' % course_id
    try:
        cursor.execute(sql)
        course = cursor.fetchone()
        return course[0]
    except:
        return -1


def attend(course_id, student_id):
    sql = "INSERT INTO 选课信息(学号,编号,选课时间) " \
          "VALUES (%s,%s,'%s')" % (course_id, student_id, str(date.today()))
    try:
        cursor.execute(sql)
        conn.commit()
        return 1
    except:
        return -1


def set_attend(s_id, c_id, attend):
    sql = "UPDATE 选课信息 SET 已修学时 = %s " \
          "WHERE 学号 = %s AND 编号 = %s" % (attend, s_id, c_id)
    try:
        cursor.execute(sql)
        conn.commit()
        return 1
    except:
        return -1


def set_grade(s_id, c_id, grade):
    sql = "UPDATE 选课信息 SET 成绩 = %s " \
          "WHERE 学号 = %s AND 编号 = %s" % (grade, s_id, c_id)
    try:
        cursor.execute(sql)
        conn.commit()
        return 1
    except:
        return -1


def set_course(course):
    try:
        sql = "UPDATE 实验课程 SET 名称 = '%s',工号 = %s," \
              "课程类型 = '%s',上课时间 = '%s',上课地点 = '%s'," \
              "学时 = %s,学分 = %s WHERE 编号 = %s" \
              % (course[1], course[2], course[3], course[4], course[5],
                 course[6], course[7], course[0])
        cursor.execute(sql)
        conn.commit()
        return 1
    except:
        return -1


def del_course(course_id):
    sql = "DELETE FROM 实验课程 WHERE 编号 = %s" % course_id
    try:
        cursor.execute(sql)
        conn.commit()
        return 1
    except:
        return -1


file2 = open('db_config.dbc', 'rb')
config = pickle.load(file2)
file2.close()
conn = pymysql.connect(**config)
cursor = conn.cursor()  # 初始化数据库
print('————————登陆选课系统————————')
# ----------------------------------------
try:
    id = int(input("请输入用户名："))
    password = input("请输密码：")
except:
    print("用户名为纯数字")
print(login(id, password))
# ----------------------------------------
# try:
#     id = int(input("教师工号："))
#     name = str(input("教师姓名："))
#     sex = str(input("教师性别："))
#     title = str(input("教师职称："))
#     major = str(input("教师专业："))
#     password = str(input("教务系统密码:"))
# except:
#     pass
# t = [id, name, sex, title, major]
# print(t_insert(teacher=t, password=password))
# -----------------------------------------
# try:
#     id = int(input("学号："))
#     name = input("姓名：")
#     birth = input("出生日期：")
#     s_time = input("入学时间")
#     major = input("专业")
#     s_class = input("班级：")
#     school = input("院系")
#     password = str(input("教务系统密码:"))
# except:
#     pass
# s = [id, name, birth, s_time, major, s_class, school]
# print(s_insert(s, password))
# -----------------------------------------
# id = input("请输入学号:")
# print(s_get_course(id))
# for eachcourse in get_course(id):
#     print("课程编号"+str(eachcourse[0]))
# -----------------------------------------
# id = input("请输入工号：")
# for ecourse in t_get_course(id):
#     print(ecourse)
#     print('\t' + '选课人数为：' + str(get_snum_in_course(ecourse[0])))
# -----------------------------------------
# id = input("学号：")
# cid = input("课程号：")
# print(attend(cid, id))
# -----------------------------------------
# name = input("名称")
# id = input("工号")
# t = input("课程类型")
# tt = input("上课时间")
# tp = input("上课地点")
# g = input("学时")
# G = input("学分")
# print(c_insert([name, id, t, tt, tp, g, G]))
# -----------------------------------------
# s_id = input("学号：")
# c_id = input("编号：")
# attend = input("已修学时：")
# print(set_attend(s_id,c_id,attend))
# -----------------------------------------
# s_id = input("学号：")
# c_id = input("编号：")
# grade = input("成绩：")
# print(set_grade(s_id, c_id, grade))
# -----------------------------------------
# cid = input("课程号：")
# name = input("名称")
# id = input("工号")
# t = input("课程类型")
# tt = input("上课时间")
# tp = input("上课地点")
# g = input("学时")
# G = input("学分")
# print(set_course([cid, name, id, t, tt, tp, g, G]))
# -----------------------------------------
# cid = input("课程编号：")
# print(del_course(cid))
# -----------------------------------------
